package bg.cinema_system.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import bg.cinema_system.global.ConnectionJDBC;
import bg.cinema_system.pojo.Projection;

public class ProjectionDAO {
	
	public static void insertProjection(Projection projection) throws SQLException, ParseException {
		Connection conn = ConnectionJDBC.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int movie_id = 0;
		try {
			ps = conn.prepareStatement("select movie_id from movies where name = ?");
			ps.setString(1, projection.getMovieName());
			rs = ps.executeQuery();
			rs.first();
			movie_id = rs.getInt("movie_id");
		} catch (SQLException e) {
			throw new SQLException(e);
		} finally {
			try {
				// Close all connections
				ps.close();
				rs.close();
			} catch (SQLException e) {
				throw new SQLException(e);
			}
		}
		
		Date date = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(projection.getTimeStart());
		PreparedStatement prepareStatement = null;
		try {
			prepareStatement = conn
					.prepareStatement("INSERT INTO Projections (projection_start, fk_movie_id, fk_hall_id) VALUES (?, ?, ?)");
			prepareStatement.setTimestamp(1, new Timestamp(date.getTime()));
			prepareStatement.setInt(2, movie_id);
			prepareStatement.setInt(3, (Integer.valueOf(projection.getHallNumer())));
			// Execute insert SQL statement
			prepareStatement.executeUpdate();
		} catch (SQLException e) {
			throw new SQLException(e);
		} finally {
			try {
				// Close all connections
				prepareStatement.close();
				conn.close();
			} catch (SQLException e) {
				throw new SQLException(e);
			}
		}
		
	}

	public static int getHallID(int projection_id) throws SQLException{
		
		Connection dbConnection = ConnectionJDBC.getConnection();

		if (dbConnection == null) {
			throw new SQLException("Class ProjectionDAO: Can't get database connection");
		}

		String sqlStatement = "SELECT projections.fk_hall_id FROM cinema.projections " 
		+ "WHERE projections.projection_id = ?";

		PreparedStatement ps = null;
		ResultSet result = null;
		int hall_id = -1;
		try {
			ps = dbConnection.prepareStatement(sqlStatement);
			ps.setInt(1, projection_id);
			
			// Get customer data from database
			result = ps.executeQuery();
			while (result.next()) {
				hall_id = result.getInt("fk_hall_id");
			}
		} catch (SQLException e) {
			throw new SQLException(e);
		} finally {
			try {
				// Close all connections
				ps.close();
				result.close();
				dbConnection.close();
			} catch (SQLException e) {
				throw new SQLException(e);
			}
		}

		return hall_id;
	}
	
	public static int getMovieID(int projection_id) throws SQLException {
		
		Connection dbConnection = ConnectionJDBC.getConnection();

		if (dbConnection == null) {
			throw new SQLException("Class ProjectionDAO: Can't get database connection");
		}

		String sqlStatement = "SELECT projections.fk_movie_id FROM cinema.projections " 
		+ "WHERE projections.projection_id = ?";

		PreparedStatement ps = null;
		ResultSet result = null;
		int movie_id = -1;
		try {
			ps = dbConnection.prepareStatement(sqlStatement);
			ps.setInt(1, projection_id);
			
			// Get customer data from database
			result = ps.executeQuery();
			
			while (result.next()) {
				movie_id = result.getInt("fk_movie_id");
			}
			
		} catch (SQLException e) {
			throw new SQLException(e);
		} finally {
			try {
				// Close all connections
				ps.close();
				result.close();
				dbConnection.close();
			} catch (SQLException e) {
				throw new SQLException(e);
			}
		}
		
		return movie_id;
	}
	
	public static Date getStartTime(int projection_id) throws SQLException {
		
		Connection dbConnection = ConnectionJDBC.getConnection();

		if (dbConnection == null) {
			throw new SQLException("Class ProjectionDAO: Can't get database connection");
		}

		String sqlStatement = "SELECT projections.projection_start FROM cinema.projections " 
		+ "WHERE projections.projection_id = ?";

		PreparedStatement ps = null;
		ResultSet result = null;
		Date projection_start = null;
		try {
			ps = dbConnection.prepareStatement(sqlStatement);
			ps.setInt(1, projection_id);
			
			// Get customer data from database
			result = ps.executeQuery();
			
			while (result.next()) {
				projection_start = new Date(result.getTimestamp("projection_start").getTime());
			}
		} catch (SQLException e) {
			throw new SQLException(e);
		} finally {
			try {
				// Close all connections
				ps.close();
				result.close();
				dbConnection.close();
			} catch (SQLException e) {
				throw new SQLException(e);
			}
		}
		
		return projection_start;
	}
}
